﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Alerter.DataSet;
using E4RPTools;

namespace Alerter
{
    public class Database
    {
        #region MessageType

        private static long GetMessageTypeId(SqlDataAccess sqlDataAccess, SqlTransaction sqlTransaction)
        {
            const string query =
                @"  select  max(Id)
                    from    TipoMensagem";
            return sqlDataAccess.ExecuteScalar(sqlTransaction, -1, query);
        }

        private static void InsertMessageType(SqlTransaction sqlTransaction, TipoMensagemDataSet.TipoMensagemRow messageType)
        {
            const string query =
                @"  insert 
                    into    TipoMensagem(Nome, Descricao) 
                    values  (@Nome, @Descricao)";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Nome", messageType.Nome),
                                        new SqlParameter("Descricao", messageType.IsDescricaoNull() ? Convert.DBNull : messageType.Descricao)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
            {
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
                messageType.Id = GetMessageTypeId(sqlDataAccess, sqlTransaction);
            }
        }

        private static void UpdateMessageType(SqlTransaction sqlTransaction, TipoMensagemDataSet.TipoMensagemRow message)
        {
            const string query =
                @"  update  TipoMensagem
                    set     Nome = @Nome,
                            Descricao = @Descricao
                    where   Id = @Id";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Id", message.Id),
                                        new SqlParameter("Nome", message.Nome),
                                        new SqlParameter("Descricao", message.IsDescricaoNull() ? Convert.DBNull : message.Descricao)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }


        public static TipoMensagemDataSet GetMessageTypes(SqlTransaction sqlTransaction)
        {
            var dataSet = new TipoMensagemDataSet();
            const string query =
                @"  select	Id, Nome, Descricao
		            from	TipoMensagem
                    order by Nome";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.TipoMensagem, query);
            return dataSet;
        }

        public static TipoMensagemDataSet GetMessageType(SqlTransaction sqlTransaction, long messageTypeId)
        {
            var dataSet = new TipoMensagemDataSet();
            const string query =
                @"  select	Id, Nome, Descricao
		            from	TipoMensagem
                    where   Id = @Id";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.TipoMensagem, query, new SqlParameter("Id", messageTypeId));
            return dataSet;
        }

        public static void SaveMessageType(SqlTransaction sqlTransaction, TipoMensagemDataSet.TipoMensagemRow messageType)
        {
            if (messageType.RowState == DataRowState.Added)
                InsertMessageType(sqlTransaction, messageType);
            else
                UpdateMessageType(sqlTransaction, messageType);
        }

        public static void DeleteMessageType(SqlTransaction sqlTransaction, long id)
        {
            const string commandText =
                @"  delete  TipoMensagem
                    where   Id = @Id";
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, commandText, new SqlParameter("Id", id));
        }

        #endregion

        public static OperadoraDataSet GetOperators(SqlTransaction sqlTransaction)
        {
            var dataSet = new OperadoraDataSet();
            const string query =
                @"  select	Id, Nome
		            from	Operadora
                    order by Nome";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Operadora, query);
            return dataSet;
        }

        public static bool ExistsOperator(SqlTransaction sqlTransaction, string name)
        {
            const string query =
                @"  select	count(*)
		            from	Operadora
                    where   upper(Nome) = upper(@Nome)";
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar(sqlTransaction, 0, query, new SqlParameter("Nome", name)) == 1;            
        }

        public static long GetOperatorId(SqlTransaction sqlTransaction, string name)
        {
            const string query =
                @"  select	Id
		            from	Operadora
                    where   upper(Nome) = upper(@Nome)";
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar(sqlTransaction, 0, query, new SqlParameter("Nome", name));
        }

        #region Contacts

        private static long GetContactId(SqlDataAccess sqlDataAccess, SqlTransaction sqlTransaction)
        {
            const string query =
                @"  select  max(Id)
                    from    Contacto";
            return sqlDataAccess.ExecuteScalar(sqlTransaction, -1, query);
        }

        private static void InsertContact(SqlTransaction sqlTransaction, ContactoDataSet.ContactoRow contact)
        {
            const string query =
                @"  insert 
                    into    Contacto(Nome, Telemovel, Telefone, IdOperadora, Email, Localidade) 
                    values  (@Nome, @Telemovel, @Telefone, @IdOperadora, @Email, @Localidade)";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Nome", contact.Nome),
                                        new SqlParameter("Telemovel", contact.IsTelemovelNull() ? Convert.DBNull : contact.Telemovel),
                                        new SqlParameter("Telefone", contact.IsTelefoneNull() ? Convert.DBNull : contact.Telefone),
                                        new SqlParameter("IdOperadora", contact.IsIdOperadoraNull() ? Convert.DBNull : contact.IdOperadora),
                                        new SqlParameter("Email", contact.IsEmailNull() ? Convert.DBNull : contact.Email),
                                        new SqlParameter("Localidade", contact.IsLocalidadeNull() ? Convert.DBNull : contact.Localidade)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
            {
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
                contact.Id = GetContactId(sqlDataAccess, sqlTransaction);
            }
        }

        private static void UpdateContact(SqlTransaction sqlTransaction, ContactoDataSet.ContactoRow contact)
        {
            const string query =
                @"  update  Contacto
                    set     Nome = @Nome,
                            Telemovel = @Telemovel,
                            Telefone = @Telefone,
                            IdOperadora = @IdOperadora,
                            Email = @Email,
                            Localidade = @Localidade
                    where   Id = @Id";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Id", contact.Id),
                                        new SqlParameter("Nome", contact.Nome),
                                        new SqlParameter("Telemovel", contact.IsTelemovelNull() ? Convert.DBNull : contact.Telemovel),
                                        new SqlParameter("Telefone", contact.IsTelefoneNull() ? Convert.DBNull : contact.Telefone),
                                        new SqlParameter("IdOperadora", contact.IsIdOperadoraNull() ? Convert.DBNull : contact.IdOperadora),
                                        new SqlParameter("Email", contact.IsEmailNull() ? Convert.DBNull : contact.Email),
                                        new SqlParameter("Localidade", contact.IsLocalidadeNull() ? Convert.DBNull : contact.Localidade)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }


        public static ContactoDataSet GetContacts(SqlTransaction sqlTransaction)
        {
            var dataSet = new ContactoDataSet();
            const string query =
                @"  select	Id, Nome, Telemovel, Telefone, IdOperadora, Email, Localidade
		            from	Contacto
                    order by Nome";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Contacto, query);
            return dataSet;
        }

        public static ContactoDataSet GetContact(SqlTransaction sqlTransaction, long contactId)
        {
            var dataSet = new ContactoDataSet();
            const string query =
                @"  select	Id, Nome, Telemovel, Telefone, IdOperadora, Email, Localidade
		            from	Contacto
                    where   Id = @Id";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Contacto, query, new SqlParameter("Id", contactId));
            return dataSet;
        }

        public static ContactoDataSet GetContactsByGroupId(SqlTransaction sqlTransaction, long groupId)
        {
            var dataSet = new ContactoDataSet();
            const string query =
                @"  select	c.Id, c.Nome, c.Telemovel, c.Telefone, c.IdOperadora, c.Email, c.Localidade
		            from	GrupoContactos gc, Contacto c
                    where   gc.IdGrupo = @Id
                            and c.Id = gc.IdContacto";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Contacto, query, new SqlParameter("Id", groupId));
            return dataSet;
        }

        public static void SaveContact(SqlTransaction sqlTransaction, ContactoDataSet.ContactoRow contact)
        {
            if (contact.RowState == DataRowState.Added)
                InsertContact(sqlTransaction, contact);
            else
                UpdateContact(sqlTransaction, contact);
        }

        public static long? GetContactIdByMobilePhone(SqlTransaction sqlTransaction, string mobilePhone)
        {
            const string query =
                @"  select	Id
		            from	Contacto
                    where   Telemovel = @Telemovel";
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar<long?>(sqlTransaction, null, query, new SqlParameter("Telemovel", mobilePhone));
        }

        public static void DeleteContact(SqlTransaction sqlTransaction, long id)
        {
            const string messageQuery =
                @"  delete  MensagemContactos
                    where   IdContacto = @Id";
            const string groupContactQuery =
                @"  delete  GrupoContactos
                    where   IdContacto = @Id";
            const string contactQuery =
                @"  delete  Contacto
                    where   Id = @Id";
            using (var sqlDataAccess = new SqlDataAccess())
            {
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, messageQuery, new SqlParameter("Id", id));
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, groupContactQuery, new SqlParameter("Id", id));
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, contactQuery, new SqlParameter("Id", id));
            }
        }

        public static bool ContactHasRelations(SqlTransaction sqlTransaction, long id)
        {
            const string messageQuery =
                @"  select  count(*)
                    from    MensagemContactos
                    where   IdContacto = @Id";
            const string groupContactQuery =
                @"  select  count(*) 
                    from    GrupoContactos
                    where   IdContacto = @Id";
            int inMessages;
            int inGroups;
            using (var sqlDataAccess = new SqlDataAccess())
            {
                inMessages = sqlDataAccess.ExecuteScalar(sqlTransaction, 0, messageQuery, new SqlParameter("Id", id));
                inGroups = sqlDataAccess.ExecuteScalar(sqlTransaction, 0, groupContactQuery, new SqlParameter("Id", id));
            }
            return inGroups + inMessages > 0;
        }


        #endregion

        #region Groups

        private static long GetGroupId(SqlDataAccess sqlDataAccess, SqlTransaction sqlTransaction)
        {
            const string query =
                @"  select  max(Id)
                    from    Grupo";
            return sqlDataAccess.ExecuteScalar(sqlTransaction, -1, query);
        }

        private static void InsertGroup(SqlTransaction sqlTransaction, GrupoDataSet.GrupoRow group)
        {
            const string query =
                @"  insert 
                    into    Grupo(Nome, Descricao) 
                    values  (@Nome, @Descricao)";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Nome", group.Nome),
                                        new SqlParameter("Descricao", group.IsDescricaoNull() ? Convert.DBNull : group.Descricao)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
            {
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
                group.Id = GetGroupId(sqlDataAccess, sqlTransaction);
            }
        }

        private static void UpdateGroup(SqlTransaction sqlTransaction, GrupoDataSet.GrupoRow group)
        {
            const string query =
                @"  update  Grupo
                    set     Nome = @Nome,
                            Descricao = @Descricao
                    where   Id = @Id";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Id", group.Id),
                                        new SqlParameter("Nome", group.Nome),
                                        new SqlParameter("Descricao", group.IsDescricaoNull() ? Convert.DBNull : group.Descricao)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }

        private static void InsertGroupContact(SqlDataAccess sqlDataAccess, SqlTransaction sqlTransaction, GrupoDataSet.ContactoRow groupContact)
        {
            const string query =
                @"  insert 
                    into    GrupoContactos(IdGrupo, IdContacto) 
                    values  (@IdGrupo, @IdContacto)";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("IdGrupo", groupContact.IdGrupo),
                                        new SqlParameter("IdContacto", groupContact.IdContacto)
                                    };
            sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }

        private static void UpdateGroupContact(SqlDataAccess sqlDataAccess, SqlTransaction sqlTransaction, GrupoDataSet.ContactoRow groupContact)
        {
            const string query =
                @"  update  GrupoContactos
                    set     IdContacto = @IdContactoN
                    where   IdGrupo = @IdGrupo
                            and IdContacto = @IdContactoV";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("IdGrupo", groupContact.IdGrupo),
                                        new SqlParameter("IdContactoN", groupContact.IdContacto),
                                        new SqlParameter("IdContactoV", Convert.ToInt64(groupContact["IdContacto", DataRowVersion.Original]))
                                    };
            sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }

        private static void DeleteGroupContact(SqlDataAccess sqlDataAccess, SqlTransaction sqlTransaction, DataRow groupContact)
        {
            const string query =
                @"  delete  GrupoContactos
                    where   IdGrupo = @IdGrupo
                            and IdContacto = @IdContacto";
            var oldGroupId = Convert.ToInt64(groupContact["IdGrupo", DataRowVersion.Original]);
            var oldContactId = Convert.ToInt64(groupContact["IdContacto", DataRowVersion.Original]);
            sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, new SqlParameter("IdGrupo", oldGroupId), new SqlParameter("IdContacto", oldContactId));
        }


        public static GrupoDataSet GetGroups(SqlTransaction sqlTransaction)
        {
            var dataSet = new GrupoDataSet();
            const string groupQuery =
                @"  select	Id, Nome, Descricao,
                            (select COUNT(*) from GrupoContactos where IdGrupo = Id) as NumeroContactos
		            from	Grupo
                    order by Nome";
            const string contactsQuery =
                @"  select	IdGrupo, IdContacto
		            from	GrupoContactos";
            using (var dataAccess = new SqlDataAccess())
            {
                dataAccess.Fill(sqlTransaction, dataSet.Grupo, groupQuery);
                dataAccess.Fill(sqlTransaction, dataSet.Contacto, contactsQuery);
            }
            return dataSet;
        }

        public static GrupoDataSet GetGroup(SqlTransaction sqlTransaction, long groupId)
        {
            var dataSet = new GrupoDataSet();
            const string groupQuery =
                @"  select	Id, Nome, Descricao,
                            (select COUNT(*) from GrupoContactos where IdGrupo = Id) as NumeroContactos
		            from	Grupo
                    where   Id = @Id
                    order by Nome";
            const string contactsQuery =
                @"  select	IdGrupo, IdContacto
		            from	GrupoContactos
                    where   IdGrupo = @Id";
            using (var dataAccess = new SqlDataAccess())
            {
                dataAccess.Fill(sqlTransaction, dataSet.Grupo, groupQuery, new SqlParameter("Id", groupId));
                dataAccess.Fill(sqlTransaction, dataSet.Contacto, contactsQuery, new SqlParameter("Id", groupId));
            }                
            return dataSet;
        }

        public static void SaveGroup(SqlTransaction sqlTransaction, GrupoDataSet.GrupoRow group)
        {
            if (group.RowState == DataRowState.Added)
                InsertGroup(sqlTransaction, group);
            else
                UpdateGroup(sqlTransaction, group);
        }

        public static void UpdateGroupContacts(SqlTransaction sqlTransaction, GrupoDataSet.ContactoDataTable groupContacts)
        {
            using (var sqlDataAccess = new SqlDataAccess())
            {
                foreach (GrupoDataSet.ContactoRow groupContact in groupContacts)
                {
                    switch (groupContact.RowState)
                    {
                        case DataRowState.Added:
                            InsertGroupContact(sqlDataAccess, sqlTransaction, groupContact);
                            break;
                        case DataRowState.Modified:
                            UpdateGroupContact(sqlDataAccess, sqlTransaction, groupContact);
                            break;
                        case DataRowState.Deleted:
                            DeleteGroupContact(sqlDataAccess, sqlTransaction, groupContact);
                            break;
                    }
                }
            }
        }

        public static void DeleteGroup(SqlTransaction sqlTransaction, long id)
        {
            const string groupQuery =
                @"  delete  Grupo
                    where   Id = @Id";
            const string contactsQuery =
                @"  delete  GrupoContactos
                    where   IdGrupo = @Id";
            const string messageQuery =
                @"  delete  MensagemContactos
                    where   IdGrupo = @Id";
            using (var sqlDataAccess = new SqlDataAccess())
            {
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, messageQuery, new SqlParameter("Id", id));
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, contactsQuery, new SqlParameter("Id", id));
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, groupQuery, new SqlParameter("Id", id));
            }
        }

        public static bool GroupHasRelations(SqlTransaction sqlTransaction, long id)
        {
            const string messageQuery =
                @"  select  count(*)
                    from    MensagemContactos
                    where   IdGrupo = @Id";
            int inMessages;
            using (var sqlDataAccess = new SqlDataAccess())
                inMessages = sqlDataAccess.ExecuteScalar(sqlTransaction, 0, messageQuery, new SqlParameter("Id", id));
            return inMessages > 0;
        }

        #endregion

        #region Message

        private static long GetMessageId(SqlDataAccess sqlDataAccess, SqlTransaction sqlTransaction)
        {
            const string query =
                @"  select  max(Id)
                    from    Mensagem";
            return sqlDataAccess.ExecuteScalar(sqlTransaction, -1, query);
        }

        private static void InsertMessage(SqlTransaction sqlTransaction, MensagemDataSet.MensagemRow message)
        {
            const string query =
                @"  insert 
                    into    Mensagem(Data, IdTipoMensagem, Descricao, Estado, Unicode) 
                    values  (@Data, @IdTipoMensagem, @Descricao, @Estado, @Unicode)";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Data", message.Data),
                                        new SqlParameter("IdTipoMensagem", message.IsIdTipoMensagemNull() ? Convert.DBNull : message.IdTipoMensagem),
                                        new SqlParameter("Descricao", message.Descricao),
                                        new SqlParameter("Estado", message.Estado),
                                        new SqlParameter("Unicode", message.Unicode)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
            {
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
                message.Id = GetMessageId(sqlDataAccess, sqlTransaction);
            }
        }

        private static void UpdateMessage(SqlTransaction sqlTransaction, MensagemDataSet.MensagemRow message)
        {
            const string query =
                @"  update  Mensagem
                    set     Data = @Data,
                            IdTipoMensagem = @IdTipoMensagem, 
                            Descricao = @Descricao, 
                            Estado = @Estado,
                            Unicode = @Unicode
                    where   Id = @Id";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Id", message.Id),
                                        new SqlParameter("Data", message.Data),
                                        new SqlParameter("IdTipoMensagem", message.IsIdTipoMensagemNull() ? Convert.DBNull : message.IdTipoMensagem),
                                        new SqlParameter("Descricao", message.Descricao),
                                        new SqlParameter("Estado", message.Estado),
                                        new SqlParameter("Unicode", message.Unicode)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }

        private static void InsertMessageContact(SqlDataAccess sqlDataAccess, SqlTransaction sqlTransaction, MensagemDataSet.ContactoRow contact)
        {
            const string query =
                @"  insert 
                    into    MensagemContactos(IdMensagem, IdContacto, IdGrupo, Estado) 
                    values  (@IdMensagem, @IdContacto, @IdGrupo, @Estado)";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("IdMensagem", contact.IdMensagem),
                                        new SqlParameter("IdContacto", contact.IsIdContactoNull() ? Convert.DBNull : contact.IdContacto),
                                        new SqlParameter("IdGrupo", contact.IsIdGrupoNull() ? Convert.DBNull : contact.IdGrupo),
                                        new SqlParameter("Estado", contact.Estado)
                                    };
            sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }

        private static void DeleteMessageContacts(SqlDataAccess sqlDataAccess, SqlTransaction sqlTransaction, long messageId)
        {
            const string query =
                @"  delete  MensagemContactos
                    where   IdMensagem = @IdMensagem";
            sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, new SqlParameter("IdMensagem", messageId));
        }


        public static long[] GetMessageIdsToProcess(SqlTransaction sqlTransaction)
        {
            const string query =
                @"  select  id
                    from    Mensagem 
                    where   estado in ('AEnviar', 'ParcialmenteEnviada')
                            and Data < @DataActual";
            using(var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteArray<long>(sqlTransaction, query, new SqlParameter("DataActual", DateTime.Now));
        }

        public static MensagemDataSet GetMessages(SqlTransaction sqlTransaction)
        {
            var dataSet = new MensagemDataSet();
            const string messageQuery =
                @"  select	Id, Data, IdTipoMensagem, Descricao, Estado, Unicode
		            from	Mensagem
                    order by Data";
            const string contactsQuery =
                @"  select	IdMensagem, IdGrupo, IdContacto, Estado, Resultado
		            from	MensagemContactos";
            using (var dataAccess = new SqlDataAccess())
            {
                dataAccess.Fill(sqlTransaction, dataSet.Mensagem, messageQuery);
                dataAccess.Fill(sqlTransaction, dataSet.Contacto, contactsQuery);
            }
            return dataSet;
        }

        public static MensagemDataSet GetMessage(SqlTransaction sqlTransaction, long id)
        {
            var dataSet = new MensagemDataSet();
            const string messageQuery =
                @"  select	Id, Data, IdTipoMensagem, Descricao, Estado, Unicode
		            from	Mensagem
                    where   Id = @Id";
            const string contactsQuery =
                @"  select  IdMensagem, IdGrupo, IdContacto, Estado, Resultado
		            from    MensagemContactos
                    where   IdMensagem = @Id";
            using (var dataAccess = new SqlDataAccess())
            {
                dataAccess.Fill(sqlTransaction, dataSet.Mensagem, messageQuery, new SqlParameter("Id", id));
                dataAccess.Fill(sqlTransaction, dataSet.Contacto, contactsQuery, new SqlParameter("Id", id));
            }
            return dataSet;
        }

        public static void SaveMessage(SqlTransaction sqlTransaction, MensagemDataSet.MensagemRow message)
        {
            if (message.RowState == DataRowState.Added)
                InsertMessage(sqlTransaction, message);
            else
                UpdateMessage(sqlTransaction, message);
        }

        public static void UpdateMessageContacts(SqlTransaction sqlTransaction, long messageId, MensagemDataSet.ContactoDataTable contacts)
        {
            using (var sqlDataAccess = new SqlDataAccess())
            {
                DeleteMessageContacts(sqlDataAccess, sqlTransaction, messageId);
                contacts.AcceptChanges();
                foreach (MensagemDataSet.ContactoRow contact in contacts)
                    contact.SetAdded();
                foreach (MensagemDataSet.ContactoRow contact in contacts)
                    InsertMessageContact(sqlDataAccess, sqlTransaction, contact);
            }
        }

        public static void UpdateMessageState(SqlTransaction sqlTransaction, long messageId, string state)
        {
            const string query =
                @"  update  Mensagem
                    set     Estado = @Estado
                    where   Id = @Id";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Id", messageId),
                                        new SqlParameter("Estado", state)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }

        public static void UpdateMessageContact(SqlTransaction sqlTransaction, long  messageId, long contactId, long? groupId, string state, string error)
        {
            const string query =
                @"  update  MensagemContactos
                    set     Estado = @Estado,
                            Resultado = @Resultado
                    where   IdMensagem = @IdMensagem
                            and IdContacto = @IdContacto 
                            and ((IdGrupo = @IdGrupo) or (IdGrupo is null and @IdGrupo is null))";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("IdMensagem", messageId),
                                        new SqlParameter("IdContacto", contactId),
                                        new SqlParameter("IdGrupo", groupId.HasValue ? groupId.Value : Convert.DBNull),
                                        new SqlParameter("Estado", state),
                                        new SqlParameter("Resultado", error)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }

        public static void DeleteMessage(SqlTransaction sqlTransaction, long id)
        {
            const string commandText =
                @"  delete  Mensagem
                    where   Id = @Id";
            using (var sqlDataAccess = new SqlDataAccess())
            {
                DeleteMessageContacts(sqlDataAccess, sqlTransaction, id);
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, commandText, new SqlParameter("Id", id));
            }
        }

        #endregion

        #region Historic

        private static long GetHistoricId(SqlDataAccess sqlDataAccess, SqlTransaction sqlTransaction)
        {
            const string query =
                @"  select  max(Id)
                    from    Historico";
            return sqlDataAccess.ExecuteScalar(sqlTransaction, -1, query);
        }

        public static void SaveHistoric(SqlTransaction sqlTransaction, HistoricoDataSet.HistoricoRow historic)
        {
            const string query =
                @"  insert 
                    into    Historico(Data, Mensagem, GrupoContacto, NomeContacto, Contacto, Estado, Resultado) 
                    values  (@Data, @Mensagem, @GrupoContacto, @NomeContacto, @Contacto, @Estado, @Resultado)";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Data", historic.Data),
                                        new SqlParameter("Mensagem", historic.Mensagem),
                                        new SqlParameter("GrupoContacto", historic.IsGrupoContactoNull() ? Convert.DBNull : historic.GrupoContacto),
                                        new SqlParameter("NomeContacto", historic.NomeContacto),
                                        new SqlParameter("Contacto", historic.Contacto),
                                        new SqlParameter("Estado", historic.Estado),
                                        new SqlParameter("Resultado", historic.IsResultadoNull() ? Convert.DBNull : historic.Resultado)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
            {
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
                historic.Id = GetHistoricId(sqlDataAccess, sqlTransaction);
            }
        }

        public static HistoricoDataSet GetHistorics(SqlTransaction sqlTransaction)
        {
            var dataSet = new HistoricoDataSet();
            const string query =
                @"  select  Id, Data, Mensagem, GrupoContacto, NomeContacto, Contacto, Estado, Resultado
                    from    Historico
                    order by  ID";
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.Fill( sqlTransaction, dataSet.Historico, query);
            return dataSet;
        }

        public static void DeleteHistoric(SqlTransaction sqlTransaction, DateTime dateTime)
        {
            const string query =
                @"  delete  Historico
                    where   year(Data) = @Year 
                            and month(Data) = @Month 
                            and day(Data) = @Day";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Year", dateTime.Year),
                                        new SqlParameter("Month", dateTime.Month),
                                        new SqlParameter("Day", dateTime.Day)                                        
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }

        #endregion

        #region Queries

        private static long GetQueryId(SqlDataAccess sqlDataAccess, SqlTransaction sqlTransaction)
        {
            const string query =
                @"  select  max(Id)
                    from    Query";
            return sqlDataAccess.ExecuteScalar(sqlTransaction, -1, query);
        }

        private static void InsertQuery(SqlTransaction sqlTransaction, QueryDataSet.QueryRow query)
        {
            const string commandText =
                @"  insert 
                    into    Query(Nome, Comando) 
                    values  (@Nome, @Comando)";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Nome", query.Nome),
                                        new SqlParameter("Comando", query.Comando)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
            {
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, commandText, sqlParameters.ToArray());
                query.Id = GetQueryId(sqlDataAccess, sqlTransaction);
            }
        }

        private static void UpdateQuery(SqlTransaction sqlTransaction, QueryDataSet.QueryRow query)
        {
            const string commandText =
                @"  update  Query
                    set     Nome = @Nome,
                            Comando = @Comando
                    where   Id = @Id";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Id", query.Id),
                                        new SqlParameter("Nome", query.Nome),
                                        new SqlParameter("Comando", query.Comando)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, commandText, sqlParameters.ToArray());
        }


        public static QueryDataSet GetQueries(SqlTransaction sqlTransaction)
        {
            var dataSet = new QueryDataSet();
            const string query =
                @"  select	Id, Nome, Comando
		            from	Query
                    order by Nome";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Query, query);
            return dataSet;
        }

        public static QueryDataSet GetQuery(SqlTransaction sqlTransaction, long queryId)
        {
            var dataSet = new QueryDataSet();
            const string query =
                @"  select	Id, Nome, Comando
		            from	Query
                    where   Id = @Id";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Query, query, new SqlParameter("Id", queryId));
            return dataSet;
        }

        public static void SaveQuery(SqlTransaction sqlTransaction, QueryDataSet.QueryRow query)
        {
            if (query.RowState == DataRowState.Added)
                InsertQuery(sqlTransaction, query);
            else
                UpdateQuery(sqlTransaction, query);
        }

        public static DataTable RunQueryCommand(SqlTransaction sqlTransaction, string commandText)
        {
            var dataTable = new DataTable();
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataTable, commandText);
            return dataTable;
        }

        public static void DeleteQuery(SqlTransaction sqlTransaction, long id)
        {
            const string commandText =
                @"  delete  Query
                    where   Id = @Id";
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, commandText, new SqlParameter("Id", id));
        }

        #endregion
    }
}
